unit clsDescontoUF;

interface

uses Dm,dbtables,db,sysutils,Grids,biblio,DBClient;

type
  TDescontoUF=Class(TObject)
    private
      Desc_codigo     : String;
    public
      Coluna          : string;
      Est_sigla       : String;
      Desc1           : double;
      Desc2           : double;
      Desc3           : double;
      Desc4           : double;
      Desc5           : double;
      Desc6           : double;
      Desc7           : double;
      Desc8           : double;
      Desc9           : double;
      Desc10          : double;
      function  Gravar(p_desc_codigo:String):boolean;
      procedure Recuperar(var p_q:TQuery;p_desc_codigo:String);
      function  Recuperar_desconto(p_uf,p_coluna:string): boolean;
      function  Excluir(p_desc_codigo:String):Boolean;
      procedure Consultar(var p_q:TQuery;p_uf:String);
      procedure Consultartodos(var p_q:TQuery);
      function  Retorna_Ultimo : string;
  end;
implementation

{ TDescontoUF }

procedure TDescontoUF.Consultar(var p_q: TQuery; p_uf: String);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select * from DescontoUF where est_sigla=:a order by Coluna');
  p_q.parambyname('a').asstring:=p_uf;
end;

procedure TDescontoUF.Consultartodos(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select * from DescontoUF order by desc_codigo, est_sigla, Coluna');
end;

function TDescontoUF.Excluir(p_desc_codigo: String): Boolean;
var tq:TQuery;
begin
  tq              := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active       := False;
  tq.close;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM DescontoUf WHERE desc_codigo=:a ');
  tq.parambyname('a').asstring := strzero(p_desc_codigo,4);
  Try
    tq.execSQL;
    Result := true;
  Except
    Result := false;
  End;
  tq.close;
  tq.Free;
end;

function TDescontoUF.Gravar(p_desc_codigo: String): boolean;
var tq:TQuery;
begin
  tq              := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active       := False;
  tq.close;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT * FROM DescontoUf where desc_codigo=:a and est_sigla=:b and coluna=:c');
  tq.parambyname('a').asstring := strzero(p_desc_codigo,4);
  tq.parambyname('b').asstring := est_sigla;
  tq.parambyname('c').asstring := coluna;
  tq.open;
  if tq.eof then begin
    tq.close;
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('INSERT INTO DescontoUf VALUES('+
               ':K_desc_codigo, '+
               ':K_Coluna, '+
               ':K_Est_sigla, '+
               ':K_Desc1, '+
               ':K_Desc2, '+
               ':K_Desc3, '+
               ':K_Desc4, '+
               ':K_Desc5, '+
               ':K_Desc6, '+
               ':K_Desc7, '+
               ':K_Desc8, '+
               ':K_Desc9, '+
               ':K_Desc10 )');
  end else begin
    tq.close;
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('UPDATE DescontoUf SET '+
               'Coluna    =:K_Coluna,'+
               'Desc1     =:K_Desc1, '+
               'Desc2     =:K_Desc2, '+
               'Desc3     =:K_Desc3, '+
               'Desc4     =:K_Desc4, '+
               'Desc5     =:K_Desc5, '+
               'Desc6     =:K_Desc6, '+
               'Desc7     =:K_Desc7, '+
               'Desc8     =:K_Desc8, '+
               'Desc9     =:K_Desc9, '+
               'Desc10    =:K_Desc10 '+
               'WHERE desc_codigo=:K_desc_codigo and Est_sigla=:K_Est_sigla');
  end;
  tq.parambyname('K_desc_codigo').asstring := strzero(p_desc_codigo,4);
  tq.parambyname('K_Coluna').asstring      := Coluna;
  tq.parambyname('K_Est_sigla').asstring   := Est_sigla;
  tq.parambyname('K_Desc1').asfloat        := Desc1;
  tq.parambyname('K_Desc2').asfloat        := Desc2;
  tq.parambyname('K_Desc3').asfloat        := Desc3;
  tq.parambyname('K_Desc4').asfloat        := Desc4;
  tq.parambyname('K_Desc5').asfloat        := Desc5;
  tq.parambyname('K_Desc6').asfloat        := Desc6;
  tq.parambyname('K_Desc7').asfloat        := Desc7;
  tq.parambyname('K_Desc8').asfloat        := Desc8;
  tq.parambyname('K_Desc9').asfloat        := Desc9;
  tq.parambyname('K_Desc10').asfloat       := Desc10;
  Try
    tq.execsql;
    result := true;
  Except
    result := false;
  End;
  tq.close;
  tq.Free;
end;


procedure TDescontoUF.Recuperar(var p_q: TQuery; p_desc_codigo: String);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select * from DescontoUF where desc_codigo=:a ');
  p_q.parambyname('a').asstring := p_desc_codigo;
end;

function TDescontoUF.Recuperar_desconto(p_uf, p_coluna: string): boolean;
var tq:TQuery;
begin
  tq              := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active       := False;
  tq.close;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT * FROM DescontoUf where Coluna=:a and Est_sigla=:b');
  tq.parambyname('a').asstring := p_coluna;
  tq.parambyname('b').asstring := p_uf;
  tq.open;
  if tq.eof then
    result := false
  else
    result := true;

  desc_codigo := tq.fieldbyname('desc_codigo').asstring;
  Coluna      := tq.fieldbyname('Coluna').asstring;
  Est_sigla   := tq.fieldbyname('Est_sigla').asstring;
  Desc1       := tq.fieldbyname('Desc1').asfloat;
  Desc2       := tq.fieldbyname('Desc2').asfloat;
  Desc3       := tq.fieldbyname('Desc3').asfloat;
  Desc4       := tq.fieldbyname('Desc4').asfloat;
  Desc5       := tq.fieldbyname('Desc5').asfloat;
  Desc6       := tq.fieldbyname('Desc6').asfloat;
  Desc7       := tq.fieldbyname('Desc6').asfloat;
  Desc8       := tq.fieldbyname('Desc8').asfloat;
  Desc9       := tq.fieldbyname('Desc9').asfloat;
  Desc10      := tq.fieldbyname('Desc10').asfloat;
  tq.Close;
  tq.Free;
end;

function TDescontoUF.Retorna_Ultimo: string;
var tq:TQuery;
    xultimo : String;
begin
  tq              := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active       := False;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT max(desc_codigo) as ultimo FROM DescontoUf ');
  tq.open;
  if tq.fieldbyname('ultimo').asstring = '' then
    result := '0001'
  else begin
    xultimo := tq.fieldbyname('ultimo').asstring;
    xultimo := inttostr(strtoint(xultimo)+1);
    xultimo := strzero(xultimo,4);
    result  := xultimo;
  end;
  tq.close;
  tq.free;
end;

end.
